use rusqlite::{named_params,Connection, Error};
use std::{env, sync::MutexGuard};
use once_cell::sync::Lazy;
use std::sync::Mutex;
use crate::entity::comm::{Page,User}; // 修改这里


// 使用 Lazy 静态变量来缓存数据库连接
static DB_CONNECTION: Lazy<Mutex<Connection>> = Lazy::new(|| {
    // 获取项目根目录的路径
    let project_dir = env::current_dir().expect("Failed to get current directory");
    // 定义数据库文件路径
    let database_path = project_dir.join("stool.db");
    // 使用静态路径创建数据库连接
    Mutex::new(Connection::open(database_path).expect("Failed to open database connection"))
});

/// 初始化用户表
pub fn init_user_table() -> Result<(), Error> {
   
    let conn = get_db_connection();
   
    // 创建用户表的 SQL 语句
    conn.execute_batch(
        "
     
         CREATE TABLE IF NOT EXISTS user (
            user_id INTEGER  PRIMARY KEY AUTOINCREMENT,
            user_name TEXT NOT NULL,
            phone TEXT NOT NULL
        )"

    )?;
    Ok(())
}
pub fn drop_table() -> Result<(), Error> {
   
    let conn = get_db_connection();
   
    // 创建用户表的 SQL 语句
    conn.execute_batch(
        "
        BEGIN; 
        drop table if EXISTS user;
        commit;"

    )?;
    Ok(())
}

pub fn get_db_connection<'a>() -> MutexGuard<'a, Connection> {
    DB_CONNECTION.lock().expect("Failed to lock database connection")
}

//实现 add_user 函数
pub fn add_user(user:User) -> Result<(), Error> {
    // 获取数据库连接
    let conn = get_db_connection();

    // 插入用户数据
    conn.execute_named(
        "INSERT INTO user (user_name, phone) VALUES (:user_name, :phone)",
        named_params!{
            ":user_name": user.user_name,
            ":phone": user.phone,

        },
    )?;
   
    Ok(())
}
pub fn query_user_page(page: i32, rows: i32,params: User) -> Result<Page<User>, Error> {
    // 获取数据库连接
    let conn = get_db_connection();

    // 计算偏移量
    let offset = (page - 1) * rows;
    // 改成动态参数  :user_name
    let mut query = "SELECT user_id, user_name, phone FROM user where 1 = 1 ".to_string();
    let mut params_vec: Vec<(&str, &dyn rusqlite::ToSql)> = vec![
        (":limit", &rows as &dyn rusqlite::ToSql),
        (":offset", &offset as &dyn rusqlite::ToSql),
    ];
    let mut total_query = "SELECT COUNT(*) FROM user where 1=1 ".to_string();
    let mut total_params_vec: Vec<(&str, &dyn rusqlite::ToSql)> = vec![ ];

    if !params.user_name.is_empty() {
        query += " and  user_name LIKE '%' || :user_name || '%'";
        params_vec.push((":user_name", &params.user_name as &dyn rusqlite::ToSql));
        total_query += " and  user_name LIKE '%' || :user_name || '%'";
        total_params_vec.push((":user_name", &params.user_name as &dyn rusqlite::ToSql));
    }

    if !params.phone.is_empty() {
        query += " and  phone LIKE '%' || :phone || '%'";
        total_query += " and  phone LIKE '%' || :phone || '%'";
        params_vec.push((":phone", &params.phone as &dyn rusqlite::ToSql));
        total_params_vec.push((":phone", &params.phone as &dyn rusqlite::ToSql));
        
    }

    query += " LIMIT :limit OFFSET :offset";
    let mut stmt = conn.prepare(&query)?;
    let user_iter = stmt.query_map_named(&params_vec, |row| {
        Ok(User {
            user_id: row.get(0)?,
            user_name: row.get(1)?,
            phone: row.get(2)?,
        })
    })?;

    // 收集查询结果
    let mut items = Vec::new();
    for user in user_iter {
        items.push(user?);
    }
    //上面两行也改为
    let mut total_stmt = conn.prepare(&total_query)?;
    let total: i64 = total_stmt.query_row_named(&total_params_vec, |row| row.get(0))?;
    Ok(Page { rows: items, total })
}